---
id: dbcontext-hight-query
title: 9.11 高级查询操作
sidebar_label: 9.11 高级查询操作
---

import useBaseUrl from "@docusaurus/useBaseUrl";
import Tabs from "@theme/Tabs";
import TabItem from "@theme/TabItem";

## 9.11.1 关联数据模型

<Tabs
  defaultValue="person"
  values={[
    { label: "Person", value: "person" },
    { label: "PersonDetail", value: "person-detail" },
    { label: "Children", value: "children" },
    { label: "Post", value: "post" },
  ]}
>
  <TabItem value="person">


```cs {38,43,48}
using Fur.DatabaseAccessor;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Fur.Core
{
    public class Person : Entity
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public Person()
        {
            CreatedTime = DateTime.Now;
            IsDeleted = false;
        }

        /// <summary>
        /// 姓名
        /// </summary>
        [MaxLength(32)]
        public string Name { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public int Age { get; set; }

        /// <summary>
        /// 住址
        /// </summary>
        public string Address { get; set; }

        /// <summary>
        /// 从表
        /// </summary>
        public PersonDetail PersonDetail { get; set; }

        /// <summary>
        /// 一对多
        /// </summary>
        public ICollection<Children> Childrens { get; set; }

        /// <summary>
        /// 多对多
        /// </summary>
        public ICollection<Post> Posts { get; set; }
    }
}
```

  </TabItem>
  <TabItem value="person-detail">


```cs {25}
using Fur.DatabaseAccessor;

namespace Fur.Core
{
    public class PersonDetail : EntityBase
    {
        /// <summary>
        /// 电话号码
        /// </summary>
        public string PhoneNumber { get; set; }

        /// <summary>
        /// QQ 号码
        /// </summary>
        public string QQ { get; set; }

        /// <summary>
        /// 外键
        /// </summary>
        public int PersonId { get; set; }

        /// <summary>
        /// 主表
        /// </summary>
        public Person Person { get; set; }
    }
}
```

  </TabItem>
  <TabItem value="children">


```cs {35}
using Fur.DatabaseAccessor;
using System;

namespace Fur.Core
{
    public class Children : Entity
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public Children()
        {
            CreatedTime = DateTime.Now;
            IsDeleted = false;
        }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 性别
        /// </summary>
        public Gender Gender { get; set; }

        /// <summary>
        /// 外键
        /// </summary>
        public int PersonId { get; set; }

        /// <summary>
        /// 主表
        /// </summary>
        public Person Person { get; set; }
    }
}
```

  </TabItem>


  <TabItem value="post">


```cs {26}
using Fur.DatabaseAccessor;
using System;
using System.Collections.Generic;

namespace Fur.Core
{
    public class Post : Entity
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public Post()
        {
            CreatedTime = DateTime.Now;
            IsDeleted = false;
        }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Person 集合
        /// </summary>
        public ICollection<Person> Persons { get; set; }
    }
}
```

  </TabItem>
</Tabs>


## 9.11.2 一对一查询

```cs
// 示例一
var person = repository.Include(u => u.Detail);

// 示例二
var person = repository.Include(u => u.Detail)
                       .Include(u => u.Post);

// 示例三
var person = repository.Include(u => u.Detail)
                            .ThenInclude(d => d.Review)
                       .Include(u => u.Post);

// 示例四
var person = repository.Include(u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))
                            .ThenInclude(d => d.Review)
                       .Include(u => u.Post);

// 示例五
var person = repository.IncludeIf(!string.IsNullOrEmpty(keyword), u => u.Detail);

// 示例六
var person = repository.IncludeIf(!string.IsNullOrEmpty(keyword), u => u.Detail)
                       .IncludeIf(age > 18), u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))
                            .ThenInclude(d => d.Review)
                       .Include(u => u.Post);
```

## 9.11.3 一对多查询

```cs
// 示例一
var person = repository.Include(u => u.Childrens);

// 参考 一对一 例子
```

:::important 特别说明

`一对一` 和 `一对多` 查询方法一样，唯一的区别是：`一对多` 采用 `ICollection<TEntity>` 定义属性。

:::

## 9.11.4 多对多查询

```cs
// 示例一
var person = repository.Include(u => u.Posts);

// 参考 一对一 例子
```

:::important 特别说明

`一对一` 和 `多对多` 查询方法一样，唯一的区别是：`多对多` 采用 `ICollection<TEntity>` 定义属性。

:::

## 9.11.5 联表查询

### 9.11.5.1 内连接 `Inner Join`

```cs
var query = from p in _personRepository.AsQueryable()
            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId
            select new PersonDto
            {
                PhoneNumber = p.PersonDetail.PhoneNumber,
                Address = p.Address,
                Age = p.Age,
                Name = p.Name,
                Id = p.Id,
                QQ = p.PersonDetail.QQ
            };
```

### 9.11.5.2 左连接 `Left Join`

```cs {2,3}
var query = from p in _personRepository.AsQueryable()
            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId into results
            from d in results.DefaultIfEmpty()
            select new PersonDto
            {
                PhoneNumber = p.PersonDetail.PhoneNumber,
                Address = p.Address,
                Age = p.Age,
                Name = p.Name,
                Id = p.Id,
                QQ = p.PersonDetail.QQ
            };
```

:::note 小提示

**`Left Join`** 和 **`Inner Join`** 不同的是，**`Left Join`** 会先将结果 **`into`** 到新的结果集然后再查询，并调用 **`DefaultIfEmpty()`** 方法。

:::

### 9.11.5.2 右连接 `Right Join`

`Right Join` 只需要将 `Left Join` 主从表位置更换即可。

## 9.11.6 分组查询

```cs
// 示例一
var query = repository.AsQueryable().GroupBy(x => new { x.Column1, x.Column2 });

// 示例二
var query = from student in repository.AsQueryable()
            group student by repository2.AsQueryable() into dateGroup
            select new ResultData()
            {
                Key = dateGroup.Key,
                Value = dateGroup.Count()
            };

// 示例三
var query = from a in repository.AsQueryable()
            join b in repository2.AsQueryable() on a.Id equals b.Aid
            join c in repository3.AsQueryable() on c.id equals b.Bid
            group emp by new { emp.Age, emp.Sex } into g
            select new {
                Peo = g.Key,
                Count = g.Count()
            };
```

## 9.11.7 合并结果集

```cs
var query = repository.AsQueryable(u => u.Id > 10)
                      .Union(
                          repository2.AsQueryable(u => u.Id <= 10)
                      );
```

## 9.11.8 查询排序

### 9.11.8.1 正序

```cs
// 示例一
var query = repository.AsQueryable()
                      .OrderBy(u => u.Id);

// 示例二
var query =repository.AsQueryable()
                     .OrderBy(u => u.Id)
                     .ThenBy(u => u.Name);
```

### 9.11.8.2 倒序

```cs
// 示例一
var query = repository.AsQueryable()
                      .OrderByDescending(u => u.Id);

// 示例二
var query =repository.AsQueryable()
                     .OrderByDescending(u => u.Id)
                     .ThenByDescending(u => u.Name);
```

### 9.11.8.3 混合倒序

```cs
// 示例一
var query = repository.AsQueryable()
                      .OrderBy(u => u.Id)
                      .OrderByDescending(u => u.Name)
                      .ThenBy(u => u.Age);
```

## 9.11.9 递归查询

<Tabs
  defaultValue="city"
  values={[
    { label: "City", value: "city" },
    { label: "CityDto", value: "citydto" }
  ]}
>
  <TabItem value="city">


```cs {12,36,41,49-55}
using Fur.DatabaseAccessor;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;

namespace Fur.Core
{
    /// <summary>
    /// 城市
    /// </summary>
    public class City : Entity, IEntityTypeBuilder<City>, IEntitySeedData<City>
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public City()
        {
            CreatedTime = DateTime.Now;
            IsDeleted = false;
        }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 上级Id
        /// </summary>
        public int? ParentId { get; set; }

        /// <summary>
        /// 上级
        /// </summary>
        public virtual City Parent { get; set; }

        /// <summary>
        /// 子集
        /// </summary>
        public virtual ICollection<City> Childrens { get; set; }

        /// <summary>
        /// 配置实体关系
        /// </summary>
        /// <param name="entityBuilder"></param>
        /// <param name="dbContext"></param>
        /// <param name="dbContextLocator"></param>
        public void Configure(EntityTypeBuilder<City> entityBuilder, DbContext dbContext, Type dbContextLocator)
        {
            entityBuilder
                .HasMany(x => x.Childrens)
                .WithOne(x => x.Parent)
                .HasForeignKey(x => x.ParentId)
                .OnDelete(DeleteBehavior.ClientSetNull); // 必须设置这一行
        }

        /// <summary>
        /// 种子数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="dbContextLocator"></param>
        /// <returns></returns>
        public IEnumerable<City> HasData(DbContext dbContext, Type dbContextLocator)
        {
            return new List<City>
            {
                new City { Id=1,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中国" },
                new City { Id=2,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="广东省",ParentId=1 },
                new City { Id=3,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中山市",ParentId=2 },
                new City { Id=4,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="珠海市",ParentId=2 },
                new City { Id=5,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="浙江省",ParentId=1 },
            };
        }
    }
}
```

  </TabItem>
  <TabItem value="citydto">


```cs
using System.Collections.Generic;

namespace Fur.Application.Persons
{
    public class CityDto
    {
        /// <summary>
        /// 逐渐
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 子集
        /// </summary>
        public ICollection<CityDto> Childrens { get; set; }
    }
}
```

  </TabItem>
</Tabs>


```cs
var cities = await repository.AsQueryable()
                             .Include(u => u.Childrens)
                             .Where(u => u.Id == 1)
                             .ToListAsync();

var dtos = cities.Adapt<List<CityDto>>();
```

## 9.11.10 动态 `Sql` 查询

`Fur` 默认不支持 动态 `Sql` 查询功能，不过可以通过第三方实现：

在 `Fur` 项目层安装 `System.Linq.Dynamic.Core` 包 [https://github.com/zzzprojects/System.Linq.Dynamic.Core](https://github.com/zzzprojects/System.Linq.Dynamic.Core)

### 9.11.10.1 动态 `Sql`

```cs
// 示例一
var query = repository.AsQueryable()
                      .Where("City == @0 and Orders.Count >= @1", "China", 10)
                      .OrderBy("CompanyName")
                      .Select("new(CompanyName as Name, Phone)");

// 示例二
var list = repository.AsQueryable()
                     .Where("Name.Contains(@0)","Fur")
                     .ToList();

// 示例三，支持 ? 语法
var customers = repository.AsQueryable()
                          .Include(c => c.Location)
                          .Where(c => c.Location?.Name == "test") // 注意 Location?.Name
                          .ToList();
```

### 9.11.10.2 动态 `Lambda`

```cs
// 示例一
var x = Expression.Parameter(typeof(int), "x");
var y = Expression.Parameter(typeof(int), "y");
var e = DynamicExpressionParser
            .ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2");

// 示例二
var e = DynamicExpressionParser.ParseLambda(
        typeof(Customer), typeof(bool),
        "City = @0 and Orders.Count >= @1",
        "London", 10);
```

## 9.11.11 性能优化

默认情况下，`EF Core` 会跟踪所有实体，也就是任何数据改变都会引起数据检查，所以如果只做查询操作，建议关闭实体跟踪功能。

`Fur` 框架提供了以下高性能实体集合：

- `DetachedEntities`：脱轨/不追踪实体
- `AsQueryable()`：不追踪实体
- `Entities.AsNoTracking()`：手动关闭实体追踪

在 `EF Core` 中，复杂查询总是会生成一个 `sql`，也就是 `AsSingleQuery()`，我们也可以设置为 `AsSplitQuery()` 切割成多个查询。

## 9.11.12 分表查询小例子

```cs
using Fur.DatabaseAccessor;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;

namespace Fur.Core
{
    public class Person : Entity, IEntityTypeBuilder<Person>
    {
        public string Name { get; set; }

        /// <summary>
        /// 配置实体关系
        /// </summary>
        /// <param name="entityBuilder"></param>
        /// <param name="dbContext"></param>
        /// <param name="dbContextLocator"></param>
        public void Configure(EntityTypeBuilder<City> entityBuilder, DbContext dbContext, Type dbContextLocator)
        {
            entityBuilder.ToSqlQuery(
              @"select * from dbo.person.2020-09-19
                union all
                select * from dbo.person.2020-09-20");
        }
    }
}
```

```cs
var posts = repository.Where(u => u.Id > 10).ToList();
```

## 9.11.12 反馈与建议

:::note 与我们交流

给 Fur 提 [Issue](https://gitee.com/monksoul/Fur/issues/new?issue)。

:::
